import pandas as pd
import numpy as np
from matplotlib import pyplot as plt
import matplotlib.patches as mpatches
import matplotlib.gridspec as gridspec
import seaborn as sns
import plotly.express as px
import plotly.io as pio
import plotly.graph_objects as go
import warnings
warnings.simplefilter(action='ignore', category=FutureWarning)

pd.set_option('display.max_colwidth', None)  # Ensures columns don't get truncated
pd.set_option('display.max_rows', None)  # Displays all rows if needed
pd.options.mode.copy_on_write = True

# load the dataset
incident = pd.read_csv(r"D:\Documents\! Resume projects\Subway\NYC Subway datasets\MTA_Subway_Major_Incidents_2023.csv")

# have a first look at our data
incident.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 503 entries, 0 to 502
Data columns (total 6 columns):
 #   Column    Non-Null Count  Dtype 
---  ------    --------------  ----- 
 0   month     503 non-null    object
 1   division  503 non-null    object
 2   line      503 non-null    object
 3   day_type  503 non-null    int64 
 4   category  503 non-null    object
 5   count     503 non-null    int64 
dtypes: int64(2), object(4)
memory usage: 23.7+ KB

incident.head()

# rename the 'count' column 
incident.rename(columns = {"count" : "incidents"}, inplace = True)

# first, get rid of systemwide values in the division and line columns
incident = incident[incident["division"]!= "Systemwide"]

# remove the division column using groupby
incidents = incident.groupby(["month", "line", "day_type", "category"]).agg({"incidents": "sum"}).reset_index()

# let's see what we have now
incidents.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 400 entries, 0 to 399
Data columns (total 5 columns):
 #   Column     Non-Null Count  Dtype 
---  ------     --------------  ----- 
 0   month      400 non-null    object
 1   line       400 non-null    object
 2   day_type   400 non-null    int64 
 3   category   400 non-null    object
 4   incidents  400 non-null    int64 
dtypes: int64(2), object(3)
memory usage: 15.8+ KB

# change date to datetime
incidents['month'] = pd.to_datetime(incidents['month'])

# change format
incidents['month'] = incidents['month'].dt.strftime('%Y-%m')
incidents['month'] = pd.to_datetime(incidents['month'])

# I want to change the data type of some columns to 'category'
incidents = incidents.astype({"line":'category', 
                              "day_type":'category', 
                              "category":'category'})

# and check our final data
incidents.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 400 entries, 0 to 399
Data columns (total 5 columns):
 #   Column     Non-Null Count  Dtype         
---  ------     --------------  -----         
 0   month      400 non-null    datetime64[ns]
 1   line       400 non-null    category      
 2   day_type   400 non-null    category      
 3   category   400 non-null    category      
 4   incidents  400 non-null    int64         
dtypes: category(3), datetime64[ns](1), int64(1)
memory usage: 8.6 KB

# load the dataset
delay = pd.read_csv(r"D:\Documents\! Resume projects\Subway\NYC Subway datasets\MTA_Subway_Trains_Delayed_2023.csv")

# have a first look at data
delay.head()

delay.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 8395 entries, 0 to 8394
Data columns (total 7 columns):
 #   Column              Non-Null Count  Dtype 
---  ------              --------------  ----- 
 0   month               8395 non-null   object
 1   division            8395 non-null   object
 2   line                8395 non-null   object
 3   day_type            8395 non-null   int64 
 4   reporting_category  8395 non-null   object
 5   subcategory         7912 non-null   object
 6   delays              8395 non-null   int64 
dtypes: int64(2), object(5)
memory usage: 459.2+ KB

# rename the 'reporting_category' column
delay.rename(columns = {"reporting_category" : "category"}, inplace = True)

# also get rid of systemwide values in the division and line columns
delay = delay[delay["division"]!= "Systemwide"]

# remove the division column using groupby
delays = delay.groupby(["month", "line", "day_type", "category", "subcategory"]).agg({"delays": "sum"}).reset_index()

# change date to datetime
delays['month'] = pd.to_datetime(delays['month'])

# change format
delays['month'] = delays['month'].dt.strftime('%Y-%m')
delays['month'] = pd.to_datetime(delays['month'])

# change the type of some other columns
delays = delays.astype({"line":'category',
                        "day_type":'category',
                        "category":'category',
                        "subcategory":'category',
                       })

# check what we have now
delays.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 7768 entries, 0 to 7767
Data columns (total 6 columns):
 #   Column       Non-Null Count  Dtype         
---  ------       --------------  -----         
 0   month        7768 non-null   datetime64[ns]
 1   line         7768 non-null   category      
 2   day_type     7768 non-null   category      
 3   category     7768 non-null   category      
 4   subcategory  7768 non-null   category      
 5   delays       7768 non-null   int64         
dtypes: category(4), datetime64[ns](1), int64(1)
memory usage: 153.6 KB

# load the final dataset
customer = pd.read_csv(r"D:\Documents\! Resume projects\Subway\NYC Subway datasets\MTA_Subway_Customer_Journey-Focused_Metrics_sum.csv")

# have a first look at data
customer.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 300 entries, 0 to 299
Data columns (total 9 columns):
 #   Column                    Non-Null Count  Dtype  
---  ------                    --------------  -----  
 0   month                     300 non-null    object 
 1   division                  300 non-null    object 
 2   line                      300 non-null    object 
 3   num_passengers            300 non-null    float64
 4   additional platform time  300 non-null    float64
 5   additional train time     300 non-null    float64
 6   total_apt                 300 non-null    float64
 7   total_att                 300 non-null    float64
 8   over_five_mins            300 non-null    float64
dtypes: float64(6), object(3)
memory usage: 21.2+ KB

pd.options.display.float_format = '{:.2f}'.format
customer.head()

customer.rename(columns = {"additional platform time" : "apt",
                           "additional train time": "att",
                           }, inplace = True)

# only leave the data for each line and get rid of total numbers
customer = customer[customer["division"]!= "Systemwide"]

# aggregate by chosen columns
customers = customer.groupby(["month", "line"]).agg(
    num_passengers = ("num_passengers", "sum"),
    apt = ("apt", "mean"),
    att = ("att", "mean"),
    total_apt = ("total_apt", "sum"),
    total_att = ("total_att", "sum"),
    over_five_mins = ("over_five_mins", "sum"),
    ).reset_index()

# change date to datetime
customers['month'] = pd.to_datetime(customers['month'])

# change format
customers['month'] = customers['month'].dt.strftime('%Y-%m')
customers['month'] = pd.to_datetime(customers['month'])

# change the line column to category
customers = customers.astype({"line":'category'})

customers.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 288 entries, 0 to 287
Data columns (total 8 columns):
 #   Column          Non-Null Count  Dtype         
---  ------          --------------  -----         
 0   month           288 non-null    datetime64[ns]
 1   line            288 non-null    category      
 2   num_passengers  288 non-null    float64       
 3   apt             288 non-null    float64       
 4   att             288 non-null    float64       
 5   total_apt       288 non-null    float64       
 6   total_att       288 non-null    float64       
 7   over_five_mins  288 non-null    float64       
dtypes: category(1), datetime64[ns](1), float64(6)
memory usage: 16.9 KB

customers[customers['total_att'] < 0].head()

# change format
incidents['month'] = incidents['month'].dt.strftime('%Y-%m')
incidents['month'] = pd.to_datetime(incidents['month'])

# remove the category column from the incidents dataset
agg_inc = incidents.groupby(["month", "line", "day_type"]).agg({"incidents": "sum"}).reset_index()

# remove the category columns from the delays dataset
agg_del = delays.groupby(["month", "line", "day_type"]).agg({"delays": "sum"}).reset_index()

# merge the two datasets on the larger one
incidents_delays = pd.merge(agg_del, agg_inc, on=["month", "line", "day_type"], how = 'left')

incidents_delays.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 552 entries, 0 to 551
Data columns (total 5 columns):
 #   Column     Non-Null Count  Dtype         
---  ------     --------------  -----         
 0   month      552 non-null    datetime64[ns]
 1   line       552 non-null    object        
 2   day_type   552 non-null    category      
 3   delays     552 non-null    int64         
 4   incidents  504 non-null    float64       
dtypes: category(1), datetime64[ns](1), float64(1), int64(1), object(1)
memory usage: 18.0+ KB

# see what rows with null values are like
df_null = incidents_delays.loc[incidents_delays.isnull().any(axis=1)]
df_null.head()

# there were no incidents on S Fkln and S Rock lines, fill them with zeros
incidents_delays['incidents'] = incidents_delays['incidents'].fillna(0)

# check if any NaNs are left
incidents_delays.isna().sum()

month        0
line         0
day_type     0
delays       0
incidents    0
dtype: int64

# again, let's make sure all data has correct types
incidents_delays.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 552 entries, 0 to 551
Data columns (total 5 columns):
 #   Column     Non-Null Count  Dtype         
---  ------     --------------  -----         
 0   month      552 non-null    datetime64[ns]
 1   line       552 non-null    object        
 2   day_type   552 non-null    category      
 3   delays     552 non-null    int64         
 4   incidents  552 non-null    float64       
dtypes: category(1), datetime64[ns](1), float64(1), int64(1), object(1)
memory usage: 18.0+ KB

incidents_delays = incidents_delays.astype({"line":'category', 
                                            "incidents":'int64'})

incidents_delays.head()

# group by month and line removing the day type division
summary_incidents = incidents_delays.groupby(["month", "line"]).agg(
    delays = ("delays", "sum"),
    incidents = ("incidents", "sum")  
).reset_index()

# let's check if everything is fine before we merge
summary_incidents.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 276 entries, 0 to 275
Data columns (total 4 columns):
 #   Column     Non-Null Count  Dtype         
---  ------     --------------  -----         
 0   month      276 non-null    datetime64[ns]
 1   line       276 non-null    category      
 2   delays     276 non-null    int64         
 3   incidents  276 non-null    int64         
dtypes: category(1), datetime64[ns](1), int64(2)
memory usage: 7.6 KB

df = pd.merge(customers, summary_incidents, on=["month", "line"], how = 'left')

df.head()

df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 288 entries, 0 to 287
Data columns (total 10 columns):
 #   Column          Non-Null Count  Dtype         
---  ------          --------------  -----         
 0   month           288 non-null    datetime64[ns]
 1   line            288 non-null    object        
 2   num_passengers  288 non-null    float64       
 3   apt             288 non-null    float64       
 4   att             288 non-null    float64       
 5   total_apt       288 non-null    float64       
 6   total_att       288 non-null    float64       
 7   over_five_mins  288 non-null    float64       
 8   delays          276 non-null    float64       
 9   incidents       276 non-null    float64       
dtypes: datetime64[ns](1), float64(8), object(1)
memory usage: 22.6+ KB

# see what rows with null values are like
df_null = df.loc[df.isnull().any(axis=1)]
df_null

# again, we have no info about incidents on line W, let's fill in with zeros
df['incidents'] = df['incidents'].fillna(0)
df['delays'] = df['delays'].fillna(0)

df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 288 entries, 0 to 287
Data columns (total 10 columns):
 #   Column          Non-Null Count  Dtype         
---  ------          --------------  -----         
 0   month           288 non-null    datetime64[ns]
 1   line            288 non-null    object        
 2   num_passengers  288 non-null    float64       
 3   apt             288 non-null    float64       
 4   att             288 non-null    float64       
 5   total_apt       288 non-null    float64       
 6   total_att       288 non-null    float64       
 7   over_five_mins  288 non-null    float64       
 8   delays          288 non-null    float64       
 9   incidents       288 non-null    float64       
dtypes: datetime64[ns](1), float64(8), object(1)
memory usage: 22.6+ KB

# The estimated percentage of customers whose journeys are not completed within 5 minutes of the scheduled time
df['over_five_min_perc'] = df['over_five_mins'] / df['num_passengers']

df.head()

df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 288 entries, 0 to 287
Data columns (total 11 columns):
 #   Column              Non-Null Count  Dtype         
---  ------              --------------  -----         
 0   month               288 non-null    datetime64[ns]
 1   line                288 non-null    object        
 2   num_passengers      288 non-null    float64       
 3   apt                 288 non-null    float64       
 4   att                 288 non-null    float64       
 5   total_apt           288 non-null    float64       
 6   total_att           288 non-null    float64       
 7   over_five_mins      288 non-null    float64       
 8   delays              288 non-null    float64       
 9   incidents           288 non-null    float64       
 10  over_five_min_perc  288 non-null    float64       
dtypes: datetime64[ns](1), float64(9), object(1)
memory usage: 24.9+ KB

df = df.astype({"line":'category',
                "delays":'int64',
                "incidents":'int64'})

df = df[['month', 'line', 'num_passengers', 'apt', 'att', 'total_apt',
       'total_att', 'delays', 'incidents', 'over_five_mins', 'over_five_min_perc']]

def plot_1(df, x, y, palette, xtitle, ytitle, figsize=(11, 4)):
    df1 = df.groupby(y)[x].sum().reset_index().sort_values(by=x, ascending=False)
    fig, ax = plt.subplots(1, 2, figsize=figsize, sharey=True, tight_layout=True)
    sns.despine(fig)

    # Plot 1: Number of delays
    sns.barplot(x=x, y=y, data=df1, order=df1[y], palette=palette, ax=ax[0])
    for container in ax[0].containers:
        ax[0].bar_label(container, fontsize=10, fontweight='bold', padding=5)

    # Plot 2: Percentage of delays
    totals = df1[x].sum()
    sns.barplot(x=x, y=y, data=df1, order=df1[y], palette=palette, ax=ax[1])
    for p in ax[1].patches:
        percentage = f'{100 * p.get_width() / totals:.1f}%'
        ax[1].annotate(percentage, (p.get_x() + p.get_width() + 5, p.get_y() + p.get_height() / 2), fontweight='bold')

    ax[0].set(xlabel='', ylabel='', title=xtitle)
    ax[1].set(xlabel='', ylabel='', title=ytitle)

    plt.show()

def plot_month(df, x, y, title, highlight_colour, non_highlight_colour):
    df1 = df.groupby(df[x].dt.month)[y].sum().reset_index()

    df1[x] = pd.Categorical(pd.to_datetime(df1[x], format='%m').dt.strftime('%B'), 
                            categories=['January', 'February', 'March', 'April', 'May', 'June', 'July', 
                                        'August', 'September', 'October', 'November', 'December'], ordered=True)
    df1 = df1.sort_values(x)
    max_cutoff, min_cutoff = df1[y].max(), df1[y].min()
    df1['colours'] = df1[y].apply(lambda val: highlight_colour if val == max_cutoff or val == min_cutoff else non_highlight_colour)

    fig, ax = plt.subplots(figsize=(12, 5), tight_layout=True)
    bars = plt.bar(df1[x], df1[y], color=df1['colours'])
    ax.spines[['right', 'top']].set_visible(False)
    ax.bar_label(bars, padding=5, color='black', fontsize=10, label_type='edge', fmt='{:.0f}', fontweight='bold')
    ax.set_title(title, fontsize=14, fontweight='bold', pad=20)

    plt.show()

def plot_mean_vert(df, x, y, color, title, figsize=(12, 5)):
    df1 = df.groupby(x)[y].mean().reset_index().sort_values(by=y, ascending=False)

    # Create the plot
    fig, ax = plt.subplots(figsize=figsize, tight_layout=True)
    sns.barplot(x=x, y=y, data=df1, order=df1[x], color=color, ax=ax).bar_label(
        ax.containers[0], fontsize=10, fontweight='bold', fmt='{:,.2f}', padding=3)

    # Set title and remove extra spines
    ax.set(xlabel='', ylabel='', title=title)
    ax.spines[['right', 'top']].set_visible(False)

    # Format x-ticks
    plt.xticks(rotation=25, ha='right')

    plt.show()

def plot_distribution(df, column_name, title, color, column):
    pio.templates.default = "plotly_white"
    fig = px.box(df, x=column_name, points="all", labels={column_name: column})
    
    fig.update_layout(
        height=400, width=900, showlegend=False,
        title={'text': title, 'y': 0.9, 'x': 0.5, 'xanchor': 'center', 'yanchor': 'top'})
    
    fig.update_traces(marker_color=color, boxmean=True)
    fig.show()

def plot_bars_with_highlights(df_pivot, normal_colors, title, threshold=1500, ncol=6, hatch='//', borderaxespad=-4.5):
    fig, ax = plt.subplots(figsize=(12, 6))
    x_pos = np.arange(len(df_pivot.index))
    
    added_labels = []
    legend_handles = []

    # Plot each row of the pivot table
    for idx, (line, row) in enumerate(df_pivot.iterrows()):
        bottom = 0
        top_categories = row.sort_values(ascending=False)[:3]
        max_value = row.max()
        max_category = row.idxmax()
        for category, value in top_categories.items():
            color = normal_colors[list(df_pivot.columns).index(category)]
            
            # Plot the bar, highlight the maximum value with hatch and edge
            if category == max_category:
                ax.bar(x_pos[idx], value, width=0.8, bottom=bottom, color=color, hatch=hatch, 
                       edgecolor='blue', linewidth=2, label=category if category not in added_labels else "")
            else:
                ax.bar(x_pos[idx], value, width=0.8, bottom=bottom, color=color, alpha=0.5, 
                       edgecolor=None, linewidth=0.5, label=category if category not in added_labels else "")
            
            # Add category to the legend if it hasn't been added
            if category not in added_labels:
                added_labels.append(category)
                legend_handles.append(mpatches.Patch(color=color, label=category))
            bottom += value

    for container in ax.containers:
        values = container.datavalues
        labels = ['{:.0f}'.format(val) if val > threshold else "" for val in values]
        ax.bar_label(container, labels=labels, label_type='center', fontsize=10, fontweight='bold', color='black')

    plt.title(title, fontsize=14)
    ax.spines[['right', 'top']].set_visible(False)
      
    # Add the legend using the previously collected handles
    plt.legend(handles=legend_handles, loc='lower center', ncol=ncol, borderaxespad=borderaxespad)
    ax.set_xticks(x_pos)  
    ax.set_xticklabels(df_pivot.index)
    ax.set(xlabel='', ylabel='')
    ax.set_xlim([x_pos[0] - 0.6, x_pos[-1] + 0.6])

    plt.tight_layout()
    plt.show()

def plot_monthly_bars_with_highlights(df_pivot, normal_colors, title, threshold=1000, ncol=6, hatch='//', borderaxespad=-4.5):
    df_pivot.index = df_pivot.index.strftime('%B')
    fig, ax = plt.subplots(figsize=(12, 6))
    x_pos = np.arange(len(df_pivot.index))
    
    added_labels = []
    legend_handles = []

    # Plot each row of the pivot table
    for idx, (month, row) in enumerate(df_pivot.iterrows()):
        bottom = 0
        max_value = row.max()
        for category, value in row.items():
            color = normal_colors[list(df_pivot.columns).index(category)]
            
            # Plot the bar, highlight the maximum value with hatch and edge
            if value == max_value:
                ax.bar(x_pos[idx], value, width=0.8, bottom=bottom, color=color, hatch=hatch, 
                       edgecolor='blue', linewidth=2, label=category if category not in added_labels else "")
            else:
                ax.bar(x_pos[idx], value, width=0.8, bottom=bottom, color=color, alpha=0.5, 
                       edgecolor=None, linewidth=0.5, label=category if category not in added_labels else "")
            
            # Add category to the legend if it hasn't been added
            if category not in added_labels:
                added_labels.append(category)
                legend_handles.append(mpatches.Patch(color=color, label=category))
            bottom += value

    # Add bar labels
    for container in ax.containers:
        values = container.datavalues
        labels = ['{:.0f}'.format(val) if val > threshold else "" for val in values]
        ax.bar_label(container, labels=labels, label_type='center', fontsize=12, fontweight='bold', color='black')

    plt.title(title, fontsize=14)
    ax.spines[['right', 'top']].set_visible(False)
    
    # Add the legend using the previously collected handles
    plt.legend(handles=legend_handles, loc='lower center', ncol=ncol, borderaxespad=borderaxespad)
    ax.set_xticks(x_pos)  
    ax.set_xticklabels(df_pivot.index)  # Use month names as x-tick labels
    ax.set(xlabel='', ylabel='')
    ax.set_xlim([x_pos[0] - 0.6, x_pos[-1] + 0.6])

    plt.tight_layout()
    plt.show()

def plot_custom_double_barplots(df, x, y1, y2, fmt1='{:.0f}', fmt2='{:.0f}', estimator=np.sum, color1='#b302b3', color2='#720e9e'):
    fig, axes = plt.subplots(2, 1, figsize=(12, 6), tight_layout=True)
    
    # First Plot (y1)
    sns.barplot(ax=axes[0], x=x, y=y1, data=df, estimator=estimator, errorbar=None, color=color1).bar_label(
        axes[0].containers[0], fontsize=10, padding=5, fmt=fmt1)
    axes[0].set_title(f'{y1.capitalize()} per {x.capitalize()}', pad=20, fontweight='bold')
    axes[0].set(xlabel='', ylabel='')
    axes[0].spines[['right', 'top']].set_visible(False)
    axes[0].tick_params(axis='x', rotation=25)
    
    # Second Plot (y2)
    sns.barplot(ax=axes[1], x=x, y=y2, data=df, estimator=estimator, errorbar=None, color=color2).bar_label(
        axes[1].containers[0], fontsize=10, padding=5, fmt=fmt2)
    axes[1].set_title(f'{y2.capitalize()} per {x.capitalize()}', pad=20, fontweight='bold')
    axes[1].set(xlabel='', ylabel='')
    axes[1].spines[['right', 'top']].set_visible(False)
    axes[1].tick_params(axis='x', rotation=25)
    
    plt.show()

pd.options.display.float_format = '{:.2f}'.format
df.describe()

plot_distribution(df, "num_passengers", "Passengers Distribution", '#720e9e', "Number of Passengers")

num_passengers = df.groupby('month')['num_passengers'].sum().reset_index()
num_passengers['month'] = pd.to_datetime(num_passengers['month'], format='%m').dt.strftime('%B')
month_order = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 
               'September', 'October', 'November', 'December']
num_passengers['month'] = pd.Categorical(num_passengers['month'], categories=month_order, ordered=True)
num_passengers = num_passengers.sort_values('month')

highlight_colour, non_highlight_colour = '#33006F', '#720e9e'
num_passengers['colours'] = num_passengers['num_passengers'].apply(
    lambda x: highlight_colour if x in [num_passengers['num_passengers'].max(), num_passengers['num_passengers'].min()]
    else non_highlight_colour)

fig, ax = plt.subplots(figsize=(12, 5), tight_layout=True)
bars = ax.bar(num_passengers['month'], num_passengers['num_passengers'], color=num_passengers['colours'])

ax.spines[['right', 'top']].set_visible(False)
ax.bar_label(bars, padding=-100, color='white', fontsize=14, label_type='edge', fmt='{:,.0f}', rotation=90)
ax.set_title('Number of Passengers by Month', fontsize=14, fontweight='bold', pad=20)
ax.set_yticklabels([]) 
plt.show()

d_line = df.groupby('line')['num_passengers'].sum()
plot_distribution(d_line, "num_passengers", "Passengers Distribution", '#720e9e', "Number of Passengers")

num_passengers = df.groupby('line')['num_passengers'].sum().reset_index().sort_values('num_passengers', ascending=False)

fig, ax = plt.subplots(figsize=(8, 8), tight_layout=True)
sns.barplot(x='num_passengers', y='line', data=num_passengers, order=num_passengers['line'], 
            hue = 'num_passengers', color='#a602a6', ax=ax, errorbar=None)

for i, (value, line) in enumerate(zip(num_passengers['num_passengers'], num_passengers['line'])):
    ax.text(value - (value * 0.02), i, line, color='white', ha='right', va='center', fontsize=8, fontweight='bold')

for container in ax.containers:
    ax.bar_label(container, fontsize=8, padding=3, fmt='{:,.0f}')
    
ax.spines[['right', 'top']].set_visible(False)
ax.set(xlabel='', ylabel='')
ax.set_title('Number of Passengers by Line', pad=10, fontsize=10, fontweight='bold')

plt.show()

plot_custom_double_barplots(df, x='line', y1='delays', y2='incidents')

sns.relplot(data=df, kind="scatter", x="incidents", y="delays", 
            hue='line', col="line", height=3, col_wrap=4, s=50, edgecolor='black', alpha = 0.9, legend=False)
plt.show()

# make a copy of dataset
df_norm = df.copy()

# Delays per 1,000 Passengers: 
# I divided the total number of delays and incidents for each line by the number of passengers and then multiplied by 1,000.
df_norm['delays (normalized)'] = df_norm['delays'] / df_norm['num_passengers'] * 1000
df_norm['incidents (normalized)'] = df_norm['incidents'] / df_norm['num_passengers'] *1000

plot_custom_double_barplots(df_norm, x='line', y1='delays (normalized)', y2='incidents (normalized)', fmt2='{:.3f}', estimator=np.mean)

plot_distribution(df, "apt", "Additional Platform Time Distribution", '#a602a6', "additional platform time")

plot_mean_vert(df, 'line', 'apt', '#a602a6', 
           'The Average Additional Time in Minutes (above Scheduled Time) Customers Wait for their Train (by Line)')

plot_custom_double_barplots(df_norm, x='line', y1='apt', y2='delays (normalized)', fmt1='{:.2f}', fmt2='{:.2f}', 
                            estimator=np.mean, color1='#b302b3', color2='#720e9e')

plot_mean_vert(df, 'line', 'att', '#a602a6', 
           'The Average Additional Time in Minutes (above Scheduled Time) Customers Spend Onboard a Train (by Line)')

plot_mean_vert(df, 'line', 'over_five_min_perc', '#a602a6', 
           'Passengers Not Completing Journey Within 5 Minutes of Scheduled Time')

overall_sum = df['delays'].sum()
print("Delays total:", overall_sum)

Delays total: 365566

overall_monthly_mean = df.groupby('month')['delays'].sum().mean()
print("Overall monthly mean of delays:", overall_monthly_mean.round(2))

Overall monthly mean of delays: 30463.83

overall_line_mean = df.groupby('line')['delays'].sum().mean()
print("Overall mean of delays by line:", overall_line_mean.round(2))

Overall mean of delays by line: 15231.92

plot_distribution(df, "delays", "Delays Distribution", '#6050DC', "delays")

custom_palette = ["#6050DC","#30286E"]
plot_1(delays, 'delays', 'day_type', custom_palette, 'Number of Delays by Day Type', 'Percentage of Delays by Day Type')

plot_month(delays, 'month', 'delays', 'Delays by month', '#6050DC', '#768493')

custom_palette = ["#6050DC","#6d5edb", "#8073de", "#9086db", "#e0e0ff"]
plot_1(delays, 'delays', 'category', custom_palette, 'Number of Delays by Category', 'Percentage of Delays by Category')

# groupby by month, category and subcategory
df_cat_month = delays.groupby(['month','category'])['delays'].sum().reset_index().sort_values('delays')
df_cat_month = df_cat_month[(df_cat_month['delays'] != 0)]

df_pivot = df_cat_month.pivot_table(index='month', columns='category', values='delays', aggfunc='sum').fillna(0)
normal_colors = ["#DC50CC", "#e0e0ff", "#9086db", "#6050DC", "#AD009A"]
plot_monthly_bars_with_highlights(df_pivot, normal_colors, title='Monthly Delays by Category (Largest Value Highlighted)', 
                                  threshold=2000, ncol=6, hatch='//')

df_cat_month = delays.groupby(['month','category'])['delays'].sum().reset_index().sort_values('month')
categories = df_cat_month['category'].unique()
colors = ["#9086db", "#AD009A", "#DC50CC", "#6050DC", "#e0e0ff"]

fig, axes = plt.subplots(2, 3, figsize=(12, 6), tight_layout=True)
plt.suptitle('Monthly Delays Trends by Category', fontsize=14)
axes = axes.flatten()

for i, category in enumerate(categories):
    category_data = df_cat_month[df_cat_month['category'] == category]
    axes[i].plot(category_data['month'].dt.month, category_data['delays'], color=colors[i], marker='o')
    axes[i].set_title(f'{category}', fontsize = 10, pad = 15)
    axes[i].set_xticks(range(1, 13))
    axes[i].set_xticklabels(range(1, 13))
    axes[i].spines[['right', 'top']].set_visible(False)
    axes[i].set(xlabel='', ylabel='')
fig.delaxes(axes[5])
plt.show()

# groupby by both category and subcategory
df_cat_subcat = delays.groupby(['category','subcategory'])['delays'].sum().reset_index().sort_values('delays', ascending=False)
df_cat_subcat = df_cat_subcat[(df_cat_subcat['delays'] != 0)]

fig, ax = plt.subplots(figsize=(12, 10), tight_layout=True)

sns.barplot(x='delays', y='subcategory', hue='category', data=df_cat_subcat, 
            order=df_cat_subcat['subcategory'], palette='gnuplot_r', ax=ax)
total_delays = df_cat_subcat['delays'].sum()
for container in ax.containers:
    labels = []
    for bar in container:
        value = bar.get_width()
        percent = value / total_delays * 100
        label = f'{int(value)} / {percent:.1f}%'
        labels.append(label)
    ax.bar_label(container, labels=labels, fontsize=10, padding=3)

ax.set(xlabel='', ylabel='')
ax.set_title('Number and Percentage of Delays by Subcategory', fontsize=12, fontweight='bold', pad=10)
ax.spines[['right', 'top']].set_visible(False)
plt.xticks(rotation=25, ha='right')
plt.show()

df_cat_subcat = delays.groupby(['category','subcategory'])['delays'].sum().reset_index().sort_values('delays')
df_cat_subcat = df_cat_subcat[(df_cat_subcat['delays'] != 0)]

colors = sns.color_palette("husl", len(df_cat_subcat['subcategory'].unique()))
def plot_bar(ax, df, title):
    total_delays = df['delays'].sum()
    percentages = (df['delays'] / total_delays) * 100
    subcategories = df['subcategory']  
    bar_plot = ax.barh(subcategories, percentages, color=colors[:len(subcategories)], height=0.9)
    ax.bar_label(bar_plot, labels=[f'{p:.2f}%' for p in percentages], fontsize=25, fontweight='bold', padding=10)
    ax.set_title(title, fontsize=28, pad=160, fontweight='bold')
    ax.set_yticks(range(len(subcategories)))
    ax.set_yticklabels(subcategories, fontsize=18)
    ax.legend(bar_plot, subcategories, loc='upper center', ncol=4, borderaxespad=-6, fontsize=20,
              title="Subcategories", title_fontsize=20)
    ax.set_xticklabels([])
    sns.despine(fig, bottom=True)

fig = plt.figure(figsize=(30,40), tight_layout=True)
gs = gridspec.GridSpec(4, 2, height_ratios=[3, 1, 2, 1])  
ax1 = fig.add_subplot(gs[0, :]) 
ax2 = fig.add_subplot(gs[1, :]) 
ax3 = fig.add_subplot(gs[2, :]) 
ax4 = fig.add_subplot(gs[3, :])  

plot_bar(ax1, df_cat_subcat[df_cat_subcat['category'] == 'Infrastructure & Equipment'], 'Infrastructure & Equipment')
plot_bar(ax2, df_cat_subcat[df_cat_subcat['category'] == 'Police & Medical'], 'Police & Medical')
plot_bar(ax3, df_cat_subcat[df_cat_subcat['category'] == 'Planned ROW Work'], 'Planned ROW Work')
plot_bar(ax4, df_cat_subcat[df_cat_subcat['category'] == 'External Factors'], 'External Factors')

plt.show()

# groupby by month, category and subcategory
df_subcat_month = delays.groupby(['month', 'category','subcategory'])['delays'].sum().reset_index().sort_values('delays', 
                                                                                                                ascending=False)
df_subcat_month = df_subcat_month[(df_subcat_month['delays'] != 0)]

def plot_monthly_delays(df, subcategory, threshold=400):
    normal_colors = sns.color_palette("husl", len(df_subcat_month['subcategory'].unique()))
    df_pivot_r = df.pivot_table(index='month', columns='subcategory', values='delays', aggfunc='sum')
    df_pivot_r.index = df_pivot_r.index.strftime('%B')
    df_pivot_r = df_pivot_r.loc[:, (df_pivot_r != 0).any()]
    
    fig, ax = plt.subplots(figsize=(12, 5))
    x_pos = np.arange(len(df_pivot_r.index))
    
    for idx, (line, row) in enumerate(df_pivot_r.iterrows()):
        bottom = 0
        top_categories = row.sort_values(ascending=False)[:5]
        max_value = row.max()
        max_category = row.idxmax()
        for category, value in top_categories.items():
            color=normal_colors[list(df_pivot_r.columns).index(category)]
            if category == max_category:
                ax.bar(x_pos[idx], value, width=0.8, bottom=bottom, color=color, hatch='//', edgecolor='green', 
                       linewidth=2, label=category if idx == 0 else "")
            else:
                ax.bar(x_pos[idx], value, width=0.8, bottom=bottom, color=color, 
                       edgecolor=None, linewidth=0.5, label=category if idx == 0 else "")
            bottom += value
         
    for container in ax.containers:
        values = container.datavalues
        labels = ['{:.0f}'.format(val) if val > threshold else "" for val in values]
        ax.bar_label(container, labels=labels, label_type='center', fontsize=10, fontweight='bold', color = 'white')

    ax.spines[['right', 'top']].set_visible(False)
    ax.set(xlabel='', ylabel='', title = subcategory)
    plt.legend(loc='lower center', ncol=5, borderaxespad = -4.5)
    ax.set_xticks(x_pos)  
    ax.set_xticklabels(df_pivot_r.index)
    ax.set_xlim([x_pos[0] - 0.6, x_pos[-1] + 0.6])
    plt.tight_layout()
    plt.show()

plot_monthly_delays(df_subcat_month[df_subcat_month['category'] == 'Infrastructure & Equipment'], 'Infrastructure & Equipment', 500)
plot_monthly_delays(df_subcat_month[df_subcat_month['category'] == 'Police & Medical'], 'Police & Medical')
plot_monthly_delays(df_subcat_month[df_subcat_month['category'] == 'Planned ROW Work'], 'Planned ROW Work')
plot_monthly_delays(df_subcat_month[df_subcat_month['category'] == 'Crew Availability'], 'Crew Availability')
plot_monthly_delays(df_subcat_month[df_subcat_month['category'] == 'External Factors'], 'External Factors', 100)

df_subcat_month = delays.groupby(['month', 'category','subcategory'])['delays'].sum().reset_index().sort_values('month')
df_subcat_month = df_subcat_month[(df_subcat_month['delays'] != 0)]
categories = df_subcat_month['subcategory'].unique()
colors = sns.color_palette("husl", len(df_subcat_month['subcategory'].unique()))

fig, axes = plt.subplots(8,3, figsize=(12, 14), tight_layout=True)
plt.suptitle('Monthly Delays Trends by Subcategory', fontsize=14, y=1)
axes = axes.flatten()

for i, category in enumerate(categories):
    category_data = df_subcat_month[df_subcat_month['subcategory'] == category]
    axes[i].plot(category_data['month'].dt.month, category_data['delays'], color=colors[i], marker='o')
    axes[i].set_title(f'{category}', fontsize = 10, pad = 15)
    axes[i].set_xticks(range(1, 13))
    axes[i].set_xticklabels(range(1, 13))
    axes[i].spines[['right', 'top']].set_visible(False)
    axes[i].set(xlabel='', ylabel='')
fig.delaxes(axes[5])
plt.show()

df1 = df.groupby('line')['delays'].sum().reset_index().sort_values(by='delays', ascending=False)
df_norm1 = df_norm.groupby('line')['delays'].mean().reset_index().sort_values(by='delays', ascending=False)

fig, axes = plt.subplots(2, 1, figsize=(12, 5), tight_layout=True)
# Delays per Line 
sns.barplot(ax=axes[0], x='line', y='delays', data=df1, estimator=np.sum, errorbar=None, 
            color="#6050DC", order = df1['line']).bar_label(axes[0].containers[0], fontsize=8, padding=5, fmt='{:.0f}')
axes[0].set_title('Delays by Line', pad=15, fontweight='bold')
axes[0].set(xlabel='', ylabel='')
axes[0].spines[['right', 'top']].set_visible(False)
axes[0].tick_params(axis='x', rotation=25)

# Delays per Line (Normalized)
sns.barplot(ax=axes[1], x='line', y='delays', data=df_norm1, estimator=np.mean, errorbar=None, 
            color="#6050DC", order = df_norm1['line']).bar_label(
    axes[1].containers[0], fontsize=10, padding=5, fmt='{:.2f}')
axes[1].set_title('Delays by Line (Normalized)', pad=10, fontweight='bold')
axes[1].set(xlabel='', ylabel='')
axes[1].spines[['right', 'top']].set_visible(False)
axes[1].tick_params(axis='x', rotation=25) 
plt.show()

line_cat_delays = delays.groupby(['line', 'category'])['delays'].sum().reset_index().sort_values('delays', 
                                                                                                 ascending = False)
df_pivot = line_cat_delays.pivot_table(index='line', columns='category', values='delays', aggfunc='sum').fillna(0)
normal_colors = sns.color_palette("husl", len(line_cat_delays['category'].unique()))

plot_bars_with_highlights(df_pivot, normal_colors, title='Delays by Category per Line (Largest Value Highlighted)', 
                          threshold=1500, ncol=6, hatch='/')

df_pivot = line_cat_delays.pivot_table(index='line', columns='category', values='delays', aggfunc='sum').fillna(0)
normal_colors = sns.color_palette("husl", len(df_pivot.columns))
fig, ax = plt.subplots(figsize=(12, 5))
for idx, category in enumerate(df_pivot.columns):
    ax.plot(df_pivot.index, df_pivot[category], color=normal_colors[idx], label=category,
            linewidth=2.5, marker='o', markersize=6, linestyle='-', markerfacecolor='white')
plt.title('Incidents by Category per Line', fontsize=14)
ax.grid(True, which='both', linestyle='--', linewidth=0.3, alpha=0.7)
ax.spines[['right', 'top']].set_visible(False)
ax.set(xlabel='Line', ylabel='Number of Incidents')
plt.legend(loc='upper right', ncol=1)
ax.set_xticks(np.arange(len(df_pivot.index)))  
ax.set_xticklabels(df_pivot.index, rotation=45)
plt.tight_layout()
plt.show()

overall_sum = df['incidents'].sum()
print("Incidents total:", overall_sum)

Incidents total: 532

overall_monthly_mean = df.groupby('month')['incidents'].sum().mean()
print("Overall monthly mean of incidents:", overall_monthly_mean.round(2))

Overall monthly mean of incidents: 44.33

overall_line_mean = df.groupby('line')['incidents'].sum().mean()
print("Overall mean of incidents by line:", overall_line_mean.round(2))

Overall mean of incidents by line: 22.17

plot_distribution(df, "incidents", "Incidents Distribution", '#2a52be', "incidents")

custom_palette = ["#2A52BE","#BFCBEC"]
plot_1(incidents, 'incidents', 'day_type', custom_palette, 'Number of Incidents by Day Type', 'Percentage of Incidents by Day Type')

plot_month(incidents, 'month', 'incidents', 'Incidents by month', '#2a52be', '#768493')

custom_palette = ["#2A52BE","#3B5BB2", "#6A86D2", "#95A9DF", "#BFCBEC", "#EAEEF9"]
plot_1(incidents, 'incidents', 'category', custom_palette, 'Number of Incidents by Category', 'Percentage of Incidents by Category')

# groupby by both category and subcategory
inc_cat_month = incidents.groupby(['month','category'])['incidents'].sum().reset_index().sort_values('incidents')
inc_cat_month = inc_cat_month[(inc_cat_month['incidents'] != 0)]

df_pivot = inc_cat_month.pivot_table(index='month', columns='category', values='incidents', aggfunc='sum').fillna(0)
normal_colors = sns.color_palette("Paired", len(inc_cat_month['category'].unique()))
plot_monthly_bars_with_highlights(df_pivot, normal_colors, title='Monthly Incidents by Category (Largest Value Highlighted)', 
                                  threshold=3, ncol=6, hatch='/')

inc_cat_month = incidents.groupby(['month','category'])['incidents'].sum().reset_index().sort_values('month')
categories = inc_cat_month['category'].unique()
colors = sns.color_palette("Paired", len(categories))

fig, axes = plt.subplots(2, 3, figsize=(12, 6), tight_layout=True)
plt.suptitle('Monthly Incident Trends by Category', fontsize=14)
axes = axes.flatten()

for i, category in enumerate(categories):
    category_data = inc_cat_month[inc_cat_month['category'] == category]
    axes[i].plot(category_data['month'].dt.month, category_data['incidents'], color=colors[i], marker='o')
    axes[i].set_title(f'{category}', fontsize = 10, pad = 15)
    axes[i].set_xticks(range(1, 13))
    axes[i].set_xticklabels(range(1, 13))
    axes[i].spines[['right', 'top']].set_visible(False)
    axes[i].set(xlabel='', ylabel='')
plt.savefig('incidents2.png')
plt.show()

df1 = df.groupby('line')['incidents'].sum().reset_index().sort_values(by='incidents', ascending=False)
df_norm1 = df_norm.groupby('line')['incidents'].mean().reset_index().sort_values(by='incidents', ascending=False)

fig, axes = plt.subplots(2, 1, figsize=(12, 5), tight_layout=True)
# Incidents per Line 
sns.barplot(ax=axes[0], x='line', y='incidents', data=df1, estimator=np.sum, errorbar=None, 
            color="#2A52BE", order = df1['line']).bar_label(axes[0].containers[0], fontsize=8, padding=5, fmt='{:.0f}')
axes[0].set_title('Incidents by Line', pad=15, fontweight='bold')
axes[0].set(xlabel='', ylabel='')
axes[0].spines[['right', 'top']].set_visible(False)
axes[0].tick_params(axis='x', rotation=25)

# Incidents per Line (Normalized)
sns.barplot(ax=axes[1], x='line', y='incidents', data=df_norm1, estimator=np.mean, errorbar=None, 
            color="#2A52BE", order = df_norm1['line']).bar_label(
    axes[1].containers[0], fontsize=8, padding=5, fmt='{:.4f}')
axes[1].set_title('Incidents by Line (Normalized)', pad=10, fontweight='bold')
axes[1].set(xlabel='', ylabel='')
axes[1].spines[['right', 'top']].set_visible(False)
axes[1].tick_params(axis='x', rotation=25) 
plt.show()

fig = px.box(df, x="line", y = 'incidents')
fig.update_layout(height=500, width=1000, showlegend=False, yaxis_title=None,  
                 title={'text': "Incidents per Line",
                        'y':0.95, 'x':0.5,
                        'xanchor': 'center', 'yanchor': 'top'})
fig.update_traces(marker_color='#2A52BE')
fig.show()

line_cat_incidents = incidents.groupby(['line', 'category'])['incidents'].sum().reset_index().sort_values('incidents', 
                                                                                                          ascending = False)
df_pivot = line_cat_incidents.pivot_table(index='line', columns='category', values='incidents', aggfunc='sum').fillna(0)
normal_colors = sns.color_palette("Paired", len(line_cat_incidents['category'].unique()))
plot_bars_with_highlights(df_pivot, normal_colors, 
                          title='Incidents by Category per Line (Largest Value Highlighted)', borderaxespad=-6,
                          threshold=1, ncol=3, hatch='/')

df_pivot = line_cat_incidents.pivot_table(index='line', columns='category', values='incidents', aggfunc='sum').fillna(0)
normal_colors = sns.color_palette("Paired", len(df_pivot.columns))
fig, ax = plt.subplots(figsize=(12, 5))
for idx, category in enumerate(df_pivot.columns):
    ax.plot(df_pivot.index, df_pivot[category], color=normal_colors[idx], label=category,
            linewidth=2.5, marker='o', markersize=6, linestyle='-', markerfacecolor='white')
plt.title('Incidents by Category per Line', fontsize=14)
ax.grid(True, which='both', linestyle='--', linewidth=0.3, alpha=0.7)
ax.spines[['right', 'top']].set_visible(False)
ax.set(xlabel='', ylabel='')
plt.legend(loc='upper right', ncol=1)
ax.set_xticks(np.arange(len(df_pivot.index))) 
plt.tight_layout()
plt.show()

df_ranks = df.copy()

# let's aggregate by necessary columns
df_ranks = df_ranks.groupby("line").agg(
    num_passengers = ("num_passengers", "sum"),
    apt = ("apt", "mean"),
    att = ("att", "mean"),
    delays = ("delays", "sum"),
    incidents = ("incidents", "sum"),
    over_five_min_perc = ("over_five_min_perc", "mean"),
    ).reset_index()
df_ranks.head()

# first, get rid of systemwide values in the division and line columns
df_ranks = df_ranks[df_ranks["line"]!= "W"]

# get delays per passenger
df_ranks['avg_del'] = df_ranks['delays'] / df_ranks['num_passengers']

# get incidents per passenger
df_ranks['avg_inc'] = df_ranks['incidents'] / df_ranks['num_passengers']

pd.options.display.float_format = '{:.6f}'.format
df_ranks.head()

# leave only the necessary columns
df_ranks_num_pass = df_ranks[['line', 'num_passengers', 'apt', 'att', 'avg_del', 'avg_inc', 'over_five_min_perc']]
df_ranks_num_pass.head()

pd.options.display.float_format = '{:.6f}'.format
df_ranks_num_pass.describe()

# Create rankings for each metric
df_ranks_num_pass['rank_passengers'] = df_ranks_num_pass['num_passengers'].rank(ascending=True)
df_ranks_num_pass['rank_delays'] = df_ranks_num_pass['avg_del'].rank(ascending=True)
df_ranks_num_pass['rank_incidents'] = df_ranks_num_pass['avg_inc'].rank(ascending=True)
df_ranks_num_pass['rank_platform_time'] = df_ranks_num_pass['apt'].rank(ascending=True)
df_ranks_num_pass['rank_train_time'] = df_ranks_num_pass['att'].rank(ascending=True)
df_ranks_num_pass['rank_over_five'] = df_ranks_num_pass['over_five_min_perc'].rank(ascending=True)

# Calculate overall rank as the average of all ranks
df_ranks_num_pass['overall_rank'] = df_ranks_num_pass[['rank_delays', 'rank_incidents', 
                                   'rank_platform_time', 'rank_train_time', 'rank_over_five']].mean(axis=1)

ranks_table_num_pass = df_ranks_num_pass[['line', 'rank_passengers', 'rank_delays', 'rank_incidents', 
                                        'rank_platform_time', 'rank_train_time', 'rank_over_five', 'overall_rank']]

# change the type of column
ranks_table_num_pass = ranks_table_num_pass.astype({"line":'object'}) 
ranks_table_num_pass.sort_values(by='overall_rank', ascending=False).\
                    style.format('{:.1f}', subset=ranks_table_num_pass.select_dtypes(include=['float', 'int']).columns).\
                    highlight_max(color='#6DD2AC').\
                    highlight_min(color='#dadada')
                    #background_gradient(cmap='Blues')

# Sort the data by overall rank
ranks_table_num_pass = ranks_table_num_pass.sort_values('overall_rank', ascending=False)

fig, ax = plt.subplots(figsize=(8, 8), tight_layout=True)
sns.barplot(x='overall_rank', y='line', data=ranks_table_num_pass, palette='crest_r', 
            order=ranks_table_num_pass['line'], errorbar=None, ax=ax)

for i, (value, line) in enumerate(zip(ranks_table_num_pass['overall_rank'], ranks_table_num_pass['line'])):
    ax.text(value - (value * 0.02), i, line, color='white', ha='right', va='center', fontsize=8, fontweight='bold')

for container in ax.containers:
    ax.bar_label(container, fontsize=8, padding=3, fmt='{:.1f}')

# Customize plot appearance
ax.set(xlabel='Overall Rank (Lower is Better)', ylabel='Subway Line')
ax.spines[['right', 'top']].set_visible(False)
ax.set_title('Overall Ranking of Subway Lines', pad=10, fontsize=10)

plt.show()

# create the dataset
r_metrics_num_pass = ranks_table_num_pass[['line', 'rank_passengers', 'rank_delays', 'rank_incidents', 
                                          'rank_platform_time', 'rank_train_time', 'rank_over_five']]
# melt it 
tidy_metrics_num_pass = r_metrics_num_pass.melt(id_vars='line')
tidy_metrics_num_pass.head()

# create dataset of overall ranking
overall_ranking_num_pass = tidy_metrics_num_pass.groupby(['line']).agg({"value": "mean"}).reset_index().sort_values('value', 
                                                                                                                    ascending=False)
# merge it with the melted dataset
tidy_num_pass = pd.merge(tidy_metrics_num_pass, overall_ranking_num_pass, on=["line"])

# Plot the grouped data: all ranks for each line
fig, ax = plt.subplots(figsize=(12, 5), tight_layout=True)
sns.barplot(x='line', y='value_x', hue='variable', data=tidy_num_pass, palette='mako', 
            order=tidy_num_pass['line'], ax=ax)

ax.set(xlabel='', ylabel='Rank (Lower is Better)')
ax.spines[['right', 'top']].set_visible(False)
ax.set_title('Comparison of Rankings Across Metrics by Subway Line', fontweight='bold', pad=15, fontsize=14)
plt.xticks(rotation=45)

plt.show()

# create a new dataframe by making a copy
data = tidy_metrics_num_pass.copy()

# rename some lines in the line columne to include an underscore
data.replace({'line':{'S 42nd':'S_42nd', 'S Fkln': 'S_Fkln', 'S Rock': 'S_Rock'}}, inplace = True)

# Function to extract datasets for all lines
def extract_all_lines_data(df):
    # Get the unique subway lines
    unique_lines = data['line'].unique()
    
    # Create a dictionary to store the data for each line
    line_data_dict = {}
    
    # Loop over each unique line and extract the corresponding data
    for line in unique_lines:
        line_data_dict[line] = data.loc[data['line'] == line]
    
    return line_data_dict
    
all_lines_data = extract_all_lines_data(data)

# Automate the creation of variables like line_1, line_2, line_A, line_B, etc.
def assign_line_variables(all_lines_data):
    for line in all_lines_data:
        # Dynamically create a variable name like line_1, line_A, etc.
        var_name = "line_" + line
        # Assign the corresponding dataframe to the dynamic variable name
        globals()[var_name] = all_lines_data[line]

assign_line_variables(all_lines_data)
line_1

# Function to plot polar bar charts for all lines in a 6x4 grid
def plot_polar_charts_grid(all_lines_data, metrics, metric_names):
    num_lines = len(all_lines_data)
    rows, cols = 6, 4 

    fig, axes = plt.subplots(rows, cols, subplot_kw={'projection': 'polar'}, figsize=(20, 33))
    axes = axes.flatten()
    colors = ["#ADE2BF", "#6DD2AC", "#43BBAD", "#35A0AA", "#3487A5", "#356D9F"]
    #colors = ["#87CEFA", "#0077c0", "#ADD8E6", "#6495ED", "#6699CC", "#0093AF"]
    width = 2 * np.pi / len(metrics)

    # Loop over all lines and plot in grid
    for idx, (line_name, line_data) in enumerate(all_lines_data.items()):
        ax = axes[idx]

        # Get the values for the line
        values = line_data['value']

        # Set up the angles for each metric
        indexes = list(range(len(metrics)))
        angles = [i * width for i in indexes]

        # Background bars
        bars_bg = ax.bar(x=angles, height=27, width=width, color='#002244', zorder=1, alpha=0.8)

        # Main bars representing the values for this line
        bars = ax.bar(x=angles, height=values, width=width, color=colors, edgecolor='white', zorder=2, alpha=0.8)

        # Add metric labels around the chart
        for angle, height, metric_name in zip(angles, values, metric_names):
            rotation_angle = np.degrees(angle)
            if angle < np.pi:
                rotation_angle -= 90
            elif angle == np.pi:
                rotation_angle -= 90
            else:
                rotation_angle += 90
            ax.text(angle, 29, metric_name, ha='center', va='center', rotation=rotation_angle, rotation_mode='anchor', fontsize=10)

        # Remove the circular axis labels
        ax.set_xticks([])

        # Adjust grid and plot limits
        ax.grid(alpha=0.2, color='#E0FFFF', lw=1)
        ax.set_ylim(0, 27)

        # Set title for each plot as the line name
        ax.set_title(f"Line {line_name}", va='bottom', fontweight='bold', pad = 20, fontsize=14)

    # Just in case - hide any extra subplots that aren't used
    for i in range(num_lines, rows * cols):
        fig.delaxes(axes[i])

    # Adjust spacing between subplots
    plt.tight_layout()
    plt.show()

# Metrics and their names for display
metrics = ['rank_passengers', 'rank_delays', 'rank_incidents', 'rank_platform_time', 'rank_train_time', 'rank_over_five']
metric_names = ['passengers','delays', 'incidents', 'platform time', 'train time', 'wait > 5 min']

# Plot all the charts in a 6x4 grid
plot_polar_charts_grid(all_lines_data, metrics, metric_names)

data label	data type	data description
month	date	Represents the time period in which the Major Incidents are being calculated (month and year).
division	text	Represents the A Division (numbered subway lines) and B Division (lettered subway lines).
line	text	Represents each subway line (1, 2, 3, 4, 5, 6, 7, A, C, E, B, D, F, M, G, J, Z, L, N, Q, R, W, S 42nd, S Rock, S Fkln).
num_passengers	numeric	Total number of passengers reported each month and on each line.
additional platform time	numeric	The average additional time in minutes (above scheduled time) customers wait for their train, reported each month and on each line.
additional train time	numeric	The average additional time in minutes (above scheduled time) customers spend onboard a train, reported each month and on each line.
total_apt	numeric	The total number of additional time in minutes (above scheduled time) customers wait for their train, reported each month and on each line.
total_att	numeric	The total number of average additional time in minutes (above scheduled time) customers spend onboard a train, reported each month and on each line.
over_five_mins	numeric	The estimated total number of customers whose journeys are not completed within 5 minutes of the scheduled time, reported each month and on each line

	month	division	line	day_type	category	count
0	2023-12	A DIVISION	1	1	Track	1
1	2023-12	A DIVISION	1	2	Other	1
2	2023-12	A DIVISION	2	1	Track	1
3	2023-12	A DIVISION	2	1	Signals	1
4	2023-12	A DIVISION	2	1	Persons on Trackbed/Police/Medical	1

	month	division	line	num_passengers	additional platform time	additional train time	total_apt	total_att	over_five_mins
0	2023-10-01	B DIVISION	E	7882217.30	1.22	0.55	9644643.30	4272768.96	1196163.70
1	2023-10-01	B DIVISION	C	5152103.20	1.60	0.52	8212485.87	2691688.69	999452.70
2	2023-03-01	B DIVISION	E	7840908.80	1.32	0.73	10261936.58	5630795.80	1370830.70
3	2023-05-01	A DIVISION	7	9603416.60	0.89	0.35	8560979.95	3299958.02	1013633.90
4	2023-12-01	B DIVISION	F	9230686.70	1.85	0.45	17077770.46	3985577.44	1957470.00

	month	line	num_passengers	apt	att	total_apt	total_att	over_five_mins
20	2023-01-01	S 42nd	735193.20	0.44	-0.10	318157.00	-76876.03	9874.50
22	2023-01-01	S Rock	39508.20	0.69	-0.93	26832.31	-36830.06	3431.60
23	2023-01-01	W	2754694.50	0.87	-0.01	2390219.99	-39946.71	262022.40
44	2023-02-01	S 42nd	598906.60	0.46	-0.10	263983.01	-57868.88	6266.20
46	2023-02-01	S Rock	36094.30	0.76	-0.97	27260.39	-35191.05	3321.00

	month	line	day_type	delays	incidents
0	2023-01-01	1	1	1125	2
1	2023-01-01	1	2	255	0
2	2023-01-01	2	1	1099	3
3	2023-01-01	2	2	539	1
4	2023-01-01	3	1	647	2

	month	division	line	day_type	reporting_category	subcategory	delays
0	2023-12-01	A DIVISION	1	1	Crew Availability	Crew Availability	221
1	2023-12-01	A DIVISION	1	1	External Factors	External Agency or Utility	13
2	2023-12-01	A DIVISION	1	1	External Factors	External Debris on Roadbed	113
3	2023-12-01	A DIVISION	1	1	Infrastructure & Equipment	Braking	24
4	2023-12-01	A DIVISION	1	1	Infrastructure & Equipment	Door-Related	39

	month	line	day_type	delays	incidents
42	2023-01-01	S Fkln	1	34	NaN
43	2023-01-01	S Fkln	2	11	NaN
44	2023-01-01	S Rock	1	97	NaN
45	2023-01-01	S Rock	2	20	NaN
88	2023-02-01	S Fkln	1	8	NaN

	month	line	num_passengers	apt	att	total_apt	total_att	over_five_mins	delays	incidents
0	2023-01-01	1	9893180.30	1.00	0.64	9914019.63	6304702.15	981412.20	1380.00	2.00
1	2023-01-01	2	8168292.50	1.15	0.30	9327605.61	2390665.29	1059623.70	1638.00	4.00
2	2023-01-01	3	4771597.60	0.83	0.30	3968047.46	1419678.26	462589.00	782.00	2.00
3	2023-01-01	4	6976875.90	1.11	0.56	7773628.07	3915283.22	863520.10	1376.00	5.00
4	2023-01-01	5	5510736.40	1.17	0.53	6459962.65	2969334.18	684848.20	983.00	3.00

	month	num_passengers	apt	att	total_apt	total_att	delays	incidents	over_five_mins	over_five_min_perc
count	288	288.00	288.00	288.00	288.00	288.00	288.00	288.00	288.00	288.00
mean	2023-06-16 12:00:00	5433090.96	1.24	0.39	7061369.64	2699878.99	1269.33	1.85	828481.75	0.14
min	2023-01-01 00:00:00	36094.30	0.44	-1.00	25886.18	-92027.62	0.00	0.00	3114.10	0.01
25%	2023-03-24 06:00:00	3084525.67	0.98	0.20	4033125.07	999808.86	746.75	0.00	496337.65	0.11
50%	2023-06-16 00:00:00	5537496.30	1.22	0.45	7490672.89	2407719.28	1265.50	1.00	885232.95	0.14
75%	2023-09-08 12:00:00	7488224.03	1.50	0.63	9913170.21	3853355.58	1745.00	3.00	1174814.70	0.19
max	2023-12-01 00:00:00	12192714.90	2.56	1.20	22123123.51	8579773.32	3391.00	10.00	1957470.00	0.26
std	NaN	2915620.72	0.39	0.38	4085681.29	2145029.28	809.79	1.95	474679.84	0.06

Category	Subcategories
Infrastructure & Equipment	- Braking - Door-Related - Fire, Smoke, Debris - Other - CE - Other - Sig - Other Infrastructure - Propulsion - Rail and Roadbed - Service Delivery - Train Brake Activation - Cause Unknown - Other Internal Disruptions - Signal Modernization Capital Project
Crew Availability	- Crew Availability
Police & Medical	- Persons on Roadbed - Public Conduct, Crime, Police Response - Sick/Injured Customer
External Factors	- External Debris on Roadbed - Inclement Weather - External Agency or Utility
Planned ROW Work	- Capital Work - Other Planned ROW - Other Planned ROW Work - Subways Maintenance - Work Equipment - Insufficient Supplement Schedule

	line	num_passengers	apt	att	delays	incidents	over_five_min_perc
0	1	118382377.90	1.11	0.68	19187	31	0.11
1	2	77773701.20	1.36	0.35	20422	46	0.14
2	3	58147189.60	1.09	0.35	10386	19	0.11
3	4	88561331.50	0.98	0.39	17073	39	0.12
4	5	68475058.40	1.09	0.34	13182	29	0.12

	line	num_passengers	apt	att	delays	incidents	over_five_min_perc	avg_del	avg_inc
0	1	118382377.900000	1.111667	0.682812	19187	31	0.111873	0.000162	0.000000
1	2	77773701.200000	1.358708	0.349335	20422	46	0.138418	0.000263	0.000001
2	3	58147189.600000	1.091646	0.345778	10386	19	0.111999	0.000179	0.000000
3	4	88561331.500000	0.984929	0.393394	17073	39	0.121358	0.000193	0.000000
4	5	68475058.400000	1.085159	0.343400	13182	29	0.121544	0.000193	0.000000

	num_passengers	apt	att	avg_del	avg_inc	over_five_min_perc
count	23.000000	23.000000	23.000000	23.000000	23.000000	23.000000
mean	66584596.743478	1.250801	0.400397	0.000372	0.000000	0.145594
std	34777212.281986	0.343276	0.371075	0.000674	0.000000	0.056137
min	562536.200000	0.469607	-0.889244	0.000056	0.000000	0.010945
25%	46839470.300000	1.088403	0.315431	0.000174	0.000000	0.112456
50%	68475058.400000	1.225275	0.490637	0.000215	0.000000	0.157344
75%	91869068.950000	1.524520	0.605124	0.000297	0.000000	0.188380
max	122188419.700000	1.729595	0.822886	0.003434	0.000001	0.226025

	line	rank_passengers	rank_delays	rank_incidents	rank_platform_time	rank_train_time	rank_over_five	overall_rank
10	D	11.0	20.0	13.0	20.0	21.0	23.0	19.4
17	N	10.0	22.0	23.0	18.0	16.0	18.0	19.4
7	A	18.0	21.0	12.0	13.0	23.0	17.0	17.2
8	B	8.0	3.0	14.0	23.0	22.0	22.0	16.8
11	E	17.0	19.0	21.0	10.0	14.0	12.0	15.2
9	C	7.0	14.0	7.0	21.0	13.0	16.0	14.2
1	2	14.0	15.0	22.0	14.0	9.0	11.0	14.2
12	F	20.0	18.0	15.0	16.0	6.0	13.0	13.6
14	JZ	4.0	17.0	8.0	17.0	10.0	15.0	13.4
18	Q	15.0	13.0	5.0	15.0	12.0	21.0	13.2
16	M	6.0	12.0	6.0	22.0	5.0	20.0	13.0
13	G	5.0	16.0	4.0	11.0	20.0	14.0	13.0
5	6	23.0	11.0	17.0	9.0	18.0	8.0	12.6
6	7	21.0	6.0	18.0	12.0	15.0	7.0	11.6
19	R	19.0	5.0	11.0	19.0	4.0	19.0	11.6
3	4	16.0	10.0	20.0	5.0	11.0	9.0	11.0
4	5	12.0	9.0	19.0	6.0	7.0	10.0	10.2
0	1	22.0	4.0	10.0	8.0	19.0	5.0	9.2
2	3	9.0	8.0	16.0	7.0	8.0	6.0	9.0
15	L	13.0	7.0	9.0	3.0	17.0	3.0	7.8
22	S Rock	1.0	23.0	1.5	4.0	1.0	4.0	6.7
21	S Fkln	2.0	2.0	1.5	2.0	3.0	2.0	2.1
20	S 42nd	3.0	1.0	3.0	1.0	2.0	1.0	1.6

	line	variable	value
0	D	rank_passengers	11.000000
1	N	rank_passengers	10.000000
2	A	rank_passengers	18.000000
3	B	rank_passengers	8.000000
4	E	rank_passengers	17.000000

	month	line	num_passengers	apt	att	total_apt	total_att	over_five_mins	delays	incidents
23	2023-01-01	W	2754694.50	0.87	-0.01	2390219.99	-39946.71	262022.40	NaN	NaN
47	2023-02-01	W	2197668.00	1.26	0.19	2770932.13	398880.32	239003.70	NaN	NaN
71	2023-03-01	W	2866517.90	1.13	0.12	3209675.39	339738.90	332993.40	NaN	NaN
95	2023-04-01	W	2529763.10	1.08	0.00	2678089.64	-22350.51	277607.20	NaN	NaN
119	2023-05-01	W	2844084.30	1.24	0.11	3450716.97	284745.40	338884.80	NaN	NaN
143	2023-06-01	W	2701657.60	1.10	0.12	2893800.94	268239.62	325695.80	NaN	NaN
167	2023-07-01	W	2547683.00	1.17	0.17	2961417.87	411102.08	315961.90	NaN	NaN
191	2023-08-01	W	2750061.00	1.23	0.17	3435218.47	479045.18	325421.60	NaN	NaN
215	2023-09-01	W	2413789.10	1.15	0.26	2777158.64	630182.48	299921.70	NaN	NaN
239	2023-10-01	W	3432986.20	0.84	0.24	2890013.92	831961.53	379563.30	NaN	NaN
263	2023-11-01	W	3371450.00	0.87	0.10	2920600.58	343433.95	331016.50	NaN	NaN
287	2023-12-01	W	2874116.60	1.25	0.29	3587496.25	797330.24	394224.30	NaN	NaN

	line	variable	value
17	1	rank_passengers	22.000000
40	1	rank_delays	4.000000
63	1	rank_incidents	10.000000
86	1	rank_platform_time	8.000000
109	1	rank_train_time	19.000000
132	1	rank_over_five	5.000000

NYC's Worst-Performing Subway Lines Through Comprehensive EDA¶

Table of Contents¶

Project goals¶

Datasets used¶

Libraries and Setup¶

Data cleaning and preparation¶

Major Incidents Data¶

Loading and First Look¶

Column Renaming¶

Removal of Unnecessary Columns¶

Data Type Conversion¶

Delays Data¶

Loading and First Look¶

Column Renaming¶

Removal of Unnecessary Columns¶

Data Type Conversion¶

Customer Journey Metrics Data¶

Loading and First Look¶

Column Renaming¶

Removal of Unnecessary Columns¶

Data Type Conversion¶

Negative values¶

Merging Datasets¶

Merge Incidents and Delays Data¶

Data Preparation¶

Merging¶

Handling Missing Values¶

Data Type Conversion¶

Merge with Customer Journey Metrics¶

Removal of Unnecessary Columns¶

Merging¶

Handling Missing Values¶

New column¶

Data Type Conversion¶

Custom Plotting Functions¶

Exploratory Data Analysis (EDA)¶

Initial Observations¶

Passengers¶

Passengers Distribution¶

Number of passengers by month¶

Subway Lines Performance¶

Number of Passengers by Line¶

Delays and Incidents by Line¶

Normalization of Delays and Incidents¶

Additional Platform Time (APT) by Line¶

Delays and APT Relationship¶

Additional Train Time (ATT) by Line¶

Passengers Not Completing Journey Within 5 Minutes of Scheduled Time¶

Delays¶

Delays Distribution¶

Delays by Day Type¶

Delays by category and type of day¶

Delays by Month¶

Delays by Category¶

Monthly Trends¶

Delays by Subcategory¶

Subcategories Percentage and Impact in Categories¶

Monthly Trends¶

Delays by Line¶

Delays by Line and Category¶

Incidents¶

Incidents Distribution¶

Incidents by Day Type¶

Incidents by Month¶

Incidents by Category¶

Monthly Trends¶

Incidents by Line¶

Worst-performing Subway Line¶

Normalization of Delays and Incidents by Passenger Volume¶

Final Rankings Table¶

Visualizations¶

Bar Plot¶

Grouped Chart¶

Polar Bar Chart¶

Observations¶

Key Insights¶

Final Recommendations¶